/*
 *接口编写处...
*如果接口需要做Action的权限验证，请在Action上使用属性
*如: [ApiActionPermission("cwXSYszk",Enums.ActionPermissionOptions.Search)]
 */
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.Entity.DomainModels;
using VOL.Finance.IServices;
using VOL.Core.Filters;
using VOL.Core.Utilities;
using VOL.Core.Extensions;
using VOL.Core.DBManager;
using System.Linq;
using Microsoft.AspNetCore.Authorization;
using OfficeOpenXml;
using System.IO;
using sysio = System.IO;
using sysnet = System.Net;
using OfficeOpenXml.Style;

namespace VOL.Finance.Controllers
{
    public partial class cwXSYszkController
    {
        private readonly IcwXSYszkService _service;//访问业务代码
        private readonly IHttpContextAccessor _httpContextAccessor;

        [ActivatorUtilitiesConstructor]
        public cwXSYszkController(
            IcwXSYszkService service,
            IHttpContextAccessor httpContextAccessor
        )
        : base(service)
        {
            _service = service;
            _httpContextAccessor = httpContextAccessor;
        }
        /**
        * 重写导入方法， 判断当前年月数据是否已审核：
        * 1、审核通过的年月不让导入
        * 2、没有审核的数据可以重新导入，重新导入为覆盖原先的数据；
        */
        [HttpPost, Route("Import1")]
        [ApiActionPermission()]
        public ActionResult Import1(List<IFormFile> fileInput, string yearandmonth)
        {

            WebResponseContent webResponse = new WebResponseContent();
            if (string.IsNullOrEmpty(yearandmonth) || yearandmonth.IsNullOrEmpty() || yearandmonth.Equals("null"))
            {
                return Json(webResponse.Error("请选择年月"));
            }
            var dd = GetFc_Yszk_2(yearandmonth);
            if (dd == 1)
            {
                return Json(webResponse.Error(yearandmonth + "数据已审核通过，不能再进行导入操作！"));
            }
            CommonData.yearMonthGlobals = yearandmonth;
            return Json(Service.Import(fileInput));
        }
        /**
         * 通过年月获取飞彩数据 判断该年月是否已审核
         */
        [HttpGet]
        public int GetFc_Yszk_2(string yearmonth)
        {
            yearmonth = yearmonth + "-01 00:00:00";
            DateTime date = Convert.ToDateTime(yearmonth);
            var dd = DBServerProvider.DbContext.Set<cwXSYszk>().Where(x => (x.duedate == date) && (x.isapproved == 1)).Select(s => s.isapproved).ToList().FirstOrDefault();
            return dd;
        }

        [HttpPost, Route("GetPageData"), AllowAnonymous]
        [ApiActionPermission()]//权限重写，在控制器中增加
        public override ActionResult GetPageData([FromBody] PageDataOptions loadData)
        {
            return base.GetPageData(loadData);
        }

        [ApiActionPermission()]//权限重写，在控制器中增加
        [HttpGet, Route("DownLoadTemplate1")]
        public ActionResult DownLoadTemplate1(string yearandmonth)
        {
            List<dynamic> list = new List<dynamic>();
            string ysrq = yearandmonth.ToString("yyyy-MM");
            string fileName = "应收账款_销司.xlsx";
            string fullFileName = ExportYszkfcExcel3(list, ysrq, fileName);
            return File(sysio.File.ReadAllBytes(fullFileName), sysnet.Mime.MediaTypeNames.Application.Octet, fileName);
        }

        [ApiActionPermission()]//权限重写，在控制器中增加
        public override ActionResult Del([FromBody] object[] keys)
        {
            return base.Del(keys);
        }

        [ApiActionPermission()]//权限重写，在控制器中增加
        public override ActionResult Update([FromBody] SaveModel saveModel)
        {
            return base.Update(saveModel);
        }
        [ApiActionPermission()]
        public override ActionResult Export([FromBody] PageDataOptions loadData)
        {
            WebResponseContent webResponse = new WebResponseContent();
            String jsonstr = loadData.Wheres;
            List<SearchParameters> parameters = loadData.Wheres.DeserializeObject<List<SearchParameters>>();
            string tsql = "SELECT CONVERT(VARCHAR(7),duedate,121) AS duedate,trackname,category,beginyearnum,endyearreceivable,customer,monthreceivable,isoverdue,iscushion,cushiontype,collectionpolicy,accountperiod,problem,measure,receivabledate,invoicingdate,personliable,aging1,aging2,aging3,aging4,aging5,aging6,returnedmoney1,returnedmoney2,returnedmoney3," +
                "agingaccount,largeregion,vin,vehicletype,arreartype,quantity,invoiceprice,writeoffsubtotal,billarredescription,collectresponsibgroup,isactualsale,isbelongsspecial FROM cw_xs_yszk where 1=1 ";
            String ysrq = "";
            foreach (SearchParameters a in parameters)
            {
                if (a.Name.Equals("duedate"))
                {
                    ysrq = a.Value;
                    tsql = tsql + " and CONVERT(VARCHAR(7),duedate,121) =  " + "\'" + Convert.ToString(a.Value) + "\'";
                }
                else
                {
                    tsql = tsql + " and " + a.Name + " = " + "\'" + Convert.ToString(a.Value) + "\'";
                }
            }
            List<dynamic> list = DBServerProvider.SqlDapper.QueryList<dynamic>(tsql, null).ToList();
            string fileName = "应收账款_销司.xlsx";
            string fullFileName = ExportYszkfcExcel3(list, ysrq, fileName);
            return File(sysio.File.ReadAllBytes(fullFileName), sysnet.Mime.MediaTypeNames.Application.Octet, fileName);
        }
        private String ExportYszkfcExcel3(List<dynamic> list, String ysrq, string fileName)
        {
            string path = null;
            path = path ?? $"Download/ExcelExport/{DateTime.Now.ToString("yyyyyMMdd")}/";
            string fullPath = path.MapPath();
            fileName = Guid.NewGuid() + "_" + fileName;
            if (!Directory.Exists(fullPath)) Directory.CreateDirectory(fullPath);
            ExcelPackage excel = new ExcelPackage();
            ExcelWorksheet sheet = excel.Workbook.Worksheets.Add("sheet1");
            sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
            sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            int thisYear = Convert.ToInt32(ysrq.Substring(0, 4));
            int lastYear = thisYear - 1;
            int thisMonth = Convert.ToInt32(ysrq.Substring(5, 2));
            int nextMonth1 = thisMonth + 1;
            nextMonth1 = CommonData.getAddMonthFormat(nextMonth1);
            int nextMonth2 = thisMonth + 2;
            nextMonth2 = CommonData.getAddMonthFormat(nextMonth2);
            int nextMonth3 = thisMonth + 3;
            nextMonth3 = CommonData.getAddMonthFormat(nextMonth3);
            sheet.Cells["A1"].Value = "应收日期";
            sheet.Cells["A1:A2"].Merge = true;
            sheet.Cells["B1"].Value = "赛道";
            sheet.Cells["B1:B2"].Merge = true;
            sheet.Cells["C1"].Value = "分类";
            sheet.Cells["C1:C2"].Merge = true;
            sheet.Cells["D1"].Value = "年初数";
            sheet.Cells["D1:D2"].Merge = true;
            sheet.Cells["E1"].Value = lastYear + "年末应收";
            sheet.Cells["E1:E2"].Merge = true;
            sheet.Cells["F1"].Value = "客户";
            sheet.Cells["F1:F2"].Merge = true;
            sheet.Cells["G1"].Value = thisYear + "年" + thisMonth + "月应收";
            sheet.Cells["G1:G2"].Merge = true;
            sheet.Cells["E1"].Value = lastYear + "年末应收";
            sheet.Cells["E1:E2"].Merge = true;
            sheet.Cells["H1"].Value = "是否逾期";
            sheet.Cells["H1:H2"].Merge = true;
            sheet.Cells["I1"].Value = "是否代垫";
            sheet.Cells["I1:I2"].Merge = true;
            sheet.Cells["J1"].Value = "代垫类型";
            sheet.Cells["J1:J2"].Merge = true;
            sheet.Cells["K1"].Value = "收款政策";
            sheet.Cells["K1:K2"].Merge = true;
            sheet.Cells["L1"].Value = "账期";
            sheet.Cells["L1:L2"].Merge = true;
            sheet.Cells["M1"].Value = "存在的问题";
            sheet.Cells["M1:M2"].Merge = true;
            sheet.Cells["N1"].Value = "措施";
            sheet.Cells["N1:N2"].Merge = true;
            sheet.Cells["O1"].Value = "应收到期日";
            sheet.Cells["O1:O2"].Merge = true;
            sheet.Cells["P1"].Value = "开票日期";
            sheet.Cells["P1:P2"].Merge = true;
            sheet.Cells["Q1"].Value = "责任人";
            sheet.Cells["Q1:Q2"].Merge = true;
            sheet.Cells["R1"].Value = "账龄";
            sheet.Cells["R1:W1"].Merge = true;
            sheet.Cells["R2"].Value = "1-3个月";
            sheet.Cells["S2"].Value = "3-6个月";
            sheet.Cells["T2"].Value = "6-12个月";
            sheet.Cells["U2"].Value = "1-2年";
            sheet.Cells["V2"].Value = "2-3年";
            sheet.Cells["W2"].Value = "3年以上";
            sheet.Cells["X1"].Value = "N+3回款预测";
            sheet.Cells["X1:Z1"].Merge = true;
            sheet.Cells["X2"].Value = nextMonth1 + "月";
            sheet.Cells["Y2"].Value = nextMonth2 + "月";
            sheet.Cells["Z2"].Value = nextMonth3 + "月";
            sheet.Cells["AA1"].Value = "账龄(天)";
            sheet.Cells["AA1:AA2"].Merge = true;
            sheet.Cells["AB1"].Value = "大区";
            sheet.Cells["AB1:AB2"].Merge = true;
            sheet.Cells["AC1"].Value = "车架号";
            sheet.Cells["AC1:AC2"].Merge = true;
            sheet.Cells["AD1"].Value = "车型";
            sheet.Cells["AD1:AD2"].Merge = true;
            sheet.Cells["AE1"].Value = "欠款类型";
            sheet.Cells["AE1:AE2"].Merge = true;
            sheet.Cells["AF1"].Value = "数量";
            sheet.Cells["AF1:AF2"].Merge = true;
            sheet.Cells["AG1"].Value = "开票价格";
            sheet.Cells["AG1:AG2"].Merge = true;
            sheet.Cells["AH1"].Value = thisYear + "年核销小计";
            sheet.Cells["AH1:AH2"].Merge = true;
            sheet.Cells["AI1"].Value = "开票及欠款说明";
            sheet.Cells["AI1:AI2"].Merge = true;
            sheet.Cells["AJ1"].Value = "清收责任组";
            sheet.Cells["AJ1:AJ2"].Merge = true;
            sheet.Cells["AK1"].Value = "是否实销";
            sheet.Cells["AK1:AK2"].Merge = true;
            sheet.Cells["AL1"].Value = "是否属于863专项";
            sheet.Cells["AL1:AL2"].Merge = true;
            for (int i = 0; i < list.Count(); i++)
            {
                int j = 0;
                int rowIndex = i + 3;
                int columnIndex = j + 1;
                foreach (KeyValuePair<string, object> col in list[i])
                {
                    string aa = col.Key;//属性
                    if (col.Value == null || col.Value.IsNullOrEmpty() || col.Value.Equals("null"))
                    {
                        sheet.Cells[rowIndex, columnIndex].Value = "";
                    }
                    else
                    {
                        string bb = col.Value.ToString() ?? "";//值
                        sheet.Cells[rowIndex, columnIndex].Value = bb;
                    }
                    columnIndex++;
                }
            }
            FileInfo file = new FileInfo(fullPath + fileName);
            excel.SaveAs(file);
            return fullPath + fileName;
        }
    }
}
